/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package myconfession.dao;

import java.math.BigInteger;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.NamingException;
import myconfession.dto.page.Page;
import myconfession.dto.page.Pages;
import myconfession.utils.SQLConnection;

/**
 *
 * @author BAOLA
 */
public class PageDAO {

    public static int createPage(Page page, String username) {
        Connection con = null;
        CallableStatement stm = null;
        try {
            con = SQLConnection.getConnection();
            stm = con.prepareCall("{call sp_addPage (?,?,?,?,?,?,?)}");
            stm.registerOutParameter(1, Types.INTEGER);
            stm.setString(2, username);
            stm.setString(3, page.getPagename());
            stm.setString(4, page.getDescription());
            stm.setString(5, page.getImageLink());
            stm.setString(6, page.getFbPageId());
            stm.setString(7, page.getHashtag());
            int row = stm.executeUpdate();
            if (row > 0) {
                return stm.getInt(1);
            }
        } catch (NamingException ex) {
            Logger.getLogger(PageDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(PageDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return 0;
    }

    public static Pages getPageList() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        Pages list = null;
        try {
            con = SQLConnection.getConnection();
            stm = con.prepareStatement("exec sp_getAllPage");
            rs = stm.executeQuery();
            list = new Pages();
            while (rs.next()) {
                int pageId = rs.getInt("id");
                String pageName = rs.getString("pageName");
                String desc = rs.getString("description");
                String imageLink = rs.getString("imageLink");
                String fbPageId = rs.getString("fbPageId");
                boolean isActive = rs.getBoolean("isActive");
                String hashtag = rs.getString("hashTag");
                Page p = new Page();
                p.setId(new BigInteger(pageId + ""));
                p.setPagename(pageName);
                p.setDescription(desc);
                p.setImageLink(imageLink);
                p.setFbPageId(fbPageId);
                p.setIsActive(isActive);
                p.setHashtag(hashtag);
                list.getPage().add(p);
            }
        } catch (NamingException ex) {
            Logger.getLogger(PageDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(PageDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return list;
    }

    public static boolean updatePage(Page page){
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = SQLConnection.getConnection();
            stm = con.prepareStatement("exec sp_updatePage ?,?,?,?,?,?");
            stm.setInt(1, page.getId().intValue());
            stm.setString(2, page.getPagename());
            stm.setString(3, page.getDescription());
            stm.setString(4, page.getImageLink());
            stm.setString(5, page.getFbPageId());
            stm.setString(6, page.getHashtag());
            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (NamingException ex) {
            Logger.getLogger(PageDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(PageDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return false;
    }

    public static boolean setActive(int pageId, boolean isActive) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = SQLConnection.getConnection();
            stm = con.prepareStatement("update Pages set isActive = ? where id = ?");
            stm.setBoolean(1, isActive);
            stm.setInt(2, pageId);
            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (NamingException ex) {
            Logger.getLogger(PageDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(PageDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return false;
    }
}
